﻿using System;
using System.Collections.Generic;
using System.Xml.Serialization;
using System.Diagnostics;
using System.Data.SqlClient;
using System.ComponentModel;
using SQLDeployAnything.Data.Helpers;

namespace SQLDeployAnything.Data.Environment
{
    [Serializable]
    [XmlRoot(ElementName="SSISConnection")]
    public class SSISConnection : Connection
    {
        public const string SQLSERVER_STRING = "SQL Server(msdb)";
        public const string FILESYSTEM_STRING = "File System";

        [Serializable]
        [TypeConverter(typeof(EnumTypeConverter))]
        public enum StorageLocation
        {
            [Description(SQLSERVER_STRING)]
            SQLServer,
            [Description(FILESYSTEM_STRING)]
            FileSystem
        }

        
        private StorageLocation mLocation;

        private string mSQLServer;
        private bool mUseTrustedConnection;
        private string mUserId;
        private string mPassword;

        public StorageLocation SSISStorage
        {
            get { return mLocation; }
            set
            {
                mLocation = value;
                OnPropertyChanged("SSISStorage");
            }
        }

        [XmlAttribute(AttributeName="Server")]
        public string SQLServer
        {
            get { return mSQLServer; }
            set
            {
                mSQLServer = value;
                OnPropertyChanged("SQLServer");
            }
        }

        [XmlAttribute(AttributeName="IntergratedSecurity")]
        public bool UseTrustedConnection
        {
            get { return mUseTrustedConnection; }
            set
            {
                mUseTrustedConnection = value;
                OnPropertyChanged("UseTrustedConnection");
            }
        }

        [XmlAttribute(AttributeName="UserId")]
        public string SQLUser
        {
            get { return mUserId; }
            set
            {
                mUserId = value;
                OnPropertyChanged("SQLUser");
            }
        }

        [XmlAttribute(AttributeName="Password")]
        public string SQLUserPassword
        {
            get { return mPassword; }
            set
            {
                mPassword = value;
                OnPropertyChanged("SQLUserPassword");
            }
        }

        protected override bool Validate()
        {
            if (!UseTrustedConnection && SSISStorage == SSISConnection.StorageLocation.FileSystem)
            {
                Trace.TraceError("Must use Windows Authentication to deploy a SSIS package to FileSystem.");
                return false;
            }
            SqlConnection con = null;
            try
            {
                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
                builder.DataSource = SQLServer;
                if (UseTrustedConnection)
                    builder.IntegratedSecurity = true;
                else
                {
                    builder.UserID = SQLUser;
                    builder.Password = SQLUserPassword;
                }
                builder.InitialCatalog = "msdb";
                builder.ApplicationName = "SSIS Connection Tester";

                con = new SqlConnection(builder.ConnectionString);
                //Or the results from this Query.  This will tell us at leat initally that the user has rights to deploy SSIS packages, though the user may still fail to deploy the package if not an admin and does not own the package being dropped/deployed.
                SqlCommand cmd = new SqlCommand("SELECT IS_SRVROLEMEMBER('sysadmin') | IS_ROLEMEMBER('db_ssisadmin') | IS_ROLEMEMBER('db_ssisltduser')");
                con.Open();
                int allowed = System.Convert.ToInt32(cmd.ExecuteScalar());
                if (allowed == 1)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Trace.TraceError("Failed to validate SSIS permission on Connection: {0}.  Due to Error: {1}", this.ConnectionName, ex.Message);
                return false;
            }
            finally
            {
                if (con != null && con.State == System.Data.ConnectionState.Open)
                    con.Close();
            }
        }

        public override string ConnectionTypeHeading
        {
            get { return "SSIS Connections"; }
        }

        public override string ConnectionDetails
        {
            get
            {
                return string.Format("Server: {0}, Storage Location: {1}, User: {2}",
                    new Object[] { this.SQLServer, (
                        this.SSISStorage == StorageLocation.FileSystem ? FILESYSTEM_STRING : SQLSERVER_STRING), 
                        (this.UseTrustedConnection ? System.Environment.UserName : this.SQLUser) });
            }
        }
    }
}
